package cn.lijian.goods.book.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import cn.itcast.commons.CommonUtils;
import cn.itcast.jdbc.TxQueryRunner;
import cn.lijian.goods.book.domain.Book;
import cn.lijian.goods.category.domain.Category;
import cn.lijian.goods.pager.Expression;
import cn.lijian.goods.pager.PageBean;
import cn.lijian.goods.pager.PageConstants;

public class BookDao {
	private QueryRunner queryRunner=new TxQueryRunner();
	/**
	 * 删除图书
	 * @param bid
	 * @author lijian
	 * @throws SQLException 
	 */
	public void delete(String bid) throws SQLException{
		String sql="delete from t_book where bid=?";
		queryRunner.update(sql,bid);
	}
	
	/**
	 * 修改图书
	 * @param book
	 * @author lijian
	 * @throws SQLException 
	 */
	public void edit(Book book) throws SQLException{
		String sql = "update t_book set bname=?,author=?,price=?,currPrice=?," +
				"discount=?,press=?,publishtime=?,edition=?,pageNum=?,wordNum=?," +
				"printtime=?,booksize=?,paper=?,cid=? where bid=?";
		Object[] params = {book.getBname(),book.getAuthor(),
				book.getPrice(),book.getCurrPrice(),book.getDiscount(),
				book.getPress(),book.getPublishtime(),book.getEdition(),
				book.getPageNum(),book.getWordNum(),book.getPrinttime(),
				book.getBooksize(),book.getPaper(), 
				book.getCategory().getCid(),book.getBid()};
		queryRunner.update(sql, params);
	}
	
	/**
	 * 查询指定分类下图书 的个数
	 * @param cid
	 * @return
	 * @throws SQLException
	 * @author lijian
	 */
	public int findBookByCategory(String cid) throws SQLException{
		String sql="select count(*) from t_book where cid=?";
		Number num=(Number)queryRunner.query(sql, new ScalarHandler(),cid);
		return num==null?0:num.intValue();
	}
	/**
	 * 按 ID 查询
	 * 	一行记录中包含了很多 book 的属性
	 * @param bid
	 * @return
	 * @throws SQLException
	 * @author lijian
	 */
	public Book findByBid(String bid) throws SQLException{
		String sql="select * from t_book b,t_category c where b.cid =c.cid and b.bid=?";
		//一行记录中包含了很多 book 的属性
		Map<String,Object> map=queryRunner.query(sql, new MapHandler(),bid);
		//把map 中除了cid 以外的映射到 book 中 
		Book book=CommonUtils.toBean(map, Book.class);
		//把 cid 属性映射到 category 中，即 这个 category 中只有 cid 属性
		Category category=CommonUtils.toBean(map, Category.class);
		book.setCategory(category);
		
		if(map.get("pid")!=null){
			Category parent=new Category();
			parent.setCid((String)(map.get("pid")));
			category.setParent(parent);
		}
		return book;
		
	} 
	
	/**
	 * 通用的查询方法
	 * @param list
	 * @param pc
	 * @return
	 * @author lijian
	 * @throws SQLException 
	 */
	private PageBean<Book> findByCriteria(List<Expression> expList,int pc) throws SQLException{
		int pageSize=PageConstants.BOOK_SIZE;
		//生成 where 子句
		StringBuffer whereSql=new StringBuffer(" where 1=1 ");
		//这个 list 存放 各个参数的值
		List<Object>params=new ArrayList<Object>();
		for(Expression ex:expList){
			whereSql.append(" and ").append(ex.getName()).append(" "+ex.getOperation()).append(" ");
			if(!ex.getOperation().trim().equals("is null")){
				whereSql.append("?");
				params.add(ex.getValue());
			}
		}
		
		/*	System.out.println("*******"+whereSql);
		System.out.println(params);*/
		
		String sql="select count(*) from t_book "+whereSql;
		Number num=(Number)queryRunner.query(sql, new ScalarHandler(),params.toArray());
		int totalRecord=num.intValue();
		
		sql="select * from t_book "+whereSql+" order by orderBy limit ?,?";;
		params.add((pc-1)*pageSize);//第一个问号，从第几个开始查询，当前页首行记录的下标
		params.add(pageSize);//第二个问号，一共查询几行
		
		
		List<Book>beanList=queryRunner.query(sql, new BeanListHandler<Book>(Book.class),params.toArray());
		
		
		PageBean<Book>pb=new PageBean<Book>();
		pb.setBeanList(beanList);
		pb.setPageCurrent(pc);
		pb.setPageSize(pageSize);
		pb.setTotalRecord(totalRecord);
		
		
		return pb;
		
	}
	
	/**
	 * 按分类查询
	 * @param cid
	 * @param pc
	 * @return
	 * @author lijian
	 * @throws SQLException 
	 */
	public PageBean<Book>findByCategory(String cid,int pc) throws SQLException{
		List<Expression>exList=new ArrayList<Expression>();
		exList.add(new Expression("cid","=",cid));
		return findByCriteria(exList, pc);
		
	}
	
	/**
	 * 按作者查询
	 * @param cid
	 * @param pc
	 * @return
	 * @author lijian
	 * @throws SQLException 
	 */
	public PageBean<Book>findByAuthor(String author,int pc) throws SQLException{
		List<Expression>exList=new ArrayList<Expression>();
		exList.add(new Expression("author","like","%"+author+"%"));
		return findByCriteria(exList, pc);
		
	}
	
	/**
	 * 按出版社查询
	 * @param cid
	 * @param pc
	 * @return
	 * @author lijian
	 * @throws SQLException 
	 */
	public PageBean<Book>findByPress(String press,int pc) throws SQLException{
		List<Expression>exList=new ArrayList<Expression>();
		exList.add(new Expression("press","like","%"+press+"%"));
		return findByCriteria(exList, pc);
		
	}
	
	/**
	 * 按多条件组合查询
	 * 	对应页面中的高级搜索
	 * @param cid
	 * @param pc
	 * @return
	 * @author lijian
	 * @throws SQLException 
	 */
	public PageBean<Book>findByCombination(Book book,int pc) throws SQLException{
		List<Expression>exList=new ArrayList<Expression>();
		exList.add(new Expression("bname","like","%"+book.getBname()+"%"));
		exList.add(new Expression("author","like","%"+book.getAuthor()+"%"));
		exList.add(new Expression("press","like","%"+book.getPress()+"%"));
		return findByCriteria(exList, pc);
		
	}
	
	/**
	 * 按书名模糊查询
	 * @param cid
	 * @param pc
	 * @return
	 * @author lijian
	 * @throws SQLException 
	 */
	public PageBean<Book>findByBname(String bname,int pc) throws SQLException{
		List<Expression>exList=new ArrayList<Expression>();
		exList.add(new Expression("bname","like","%"+bname+"%"));
		return findByCriteria(exList, pc);
	}
	
	
/*	public static void main(String[] args) {
		BookDao bookDao=new BookDao();
		List<Expression>list=new ArrayList<Expression>();
		list.add(new Expression("bid","=","1"));
		list.add(new Expression("bname","like","%java%"));
		list.add(new Expression("edition"," is null",null));
		
		//bookDao.findByCriteria(list, 3);
	}*/
	/**
	 * 添加图书
	 * @param book
	 * @author lijian
	 * @throws SQLException 
	 */
	public void add(Book book) throws SQLException {
		// TODO Auto-generated method stub
		String sql = "insert into t_book(bid,bname,author,price,currPrice," +
				"discount,press,publishtime,edition,pageNum,wordNum,printtime," +
				"booksize,paper,cid,image_w,image_b)" +
				" values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		Object[] params = {book.getBid(),book.getBname(),book.getAuthor(),
				book.getPrice(),book.getCurrPrice(),book.getDiscount(),
				book.getPress(),book.getPublishtime(),book.getEdition(),
				book.getPageNum(),book.getWordNum(),book.getPrinttime(),
				book.getBooksize(),book.getPaper(), book.getCategory().getCid(),
				book.getImage_w(),book.getImage_b()};
		queryRunner.update(sql,params);
	}
	
}
